<?php
/**
 * Route
 *
 * Класс Route обрабатывает данные, связанные с маршрутами
 * пока он служит только для запоминания маршрутов, которые уже парсили
 *
 */
class Route extends ActiveRecord {
	public $tableName = 'route';
	public $pkName = 'ID_route';
  
	protected $arrSettings = array('created_at' => true);
	
  /**
   * Проверяет нет ли записи о маршруте с переданными id городов
   * 
   */
 public function isAlreadyExists($IdOneCity, $IdAnotherCity) {
  	Service_Debug::setDebug();
  	$objDB = Service_Database::instance();//Получаем подключение к БД
  	$stmt = "SELECT `ID_route` FROM `route`
  		WHERE `route`.`ID_one_city`='" . $IdOneCity . "' AND `route`.`ID_another_city`='" . $IdAnotherCity . "' OR `route`.`ID_one_city`='" . $IdAnotherCity . "' 
  		AND `route`.`ID_another_city`='" . $IdOneCity . "'";
  	return $objDB->selectSimpleField($stmt);
  }
  
  /**
   * Функция получает длину маршрута
   */
  public static function getRouteLength($idOneCity, $idAnotherCity) {
  	Service_Debug::setDebug();
  	$objDB = Service_Database::instance();
  	$stmt = "SELECT SUM(`distance`.`distance`)
		  	FROM `route`
		  	INNER JOIN `distance_route` USING(`ID_route`)
		  	INNER JOIN `distance` USING (`ID_distance`)
		  	WHERE (`route`.`ID_one_city` = '" . $idOneCity . "' AND   `route`.`ID_another_city` = '" . $idAnotherCity . "') OR
		  	(`route`.`ID_one_city` = '" . $idAnotherCity . "' AND  `route`.`ID_another_city` = '" . $idOneCity . "')";
  	return $objDB->selectSimpleField($stmt);
  }
  
  /**
   * Функция получает массив маршрута по id городов
   *
   * @param int $idOneCity - id одного города
   * @param int $idAnotherCity - id другого города
   */
  public static function getRouteArrayByIds($idOneCity, $idAnotherCity) {
  	Service_Debug::setDebug();
  	$objDB = Service_Database::instance();
  	$stmt = "SELECT `distance`.`ID_one_city`,`distance`.`ID_another_city`,`distance`.`distance`,`distance`.`road_type`,`c1`.`city_name` AS `one_city_name`,`c1`.`latitude` AS `one_city_latitude`,`c1`.`longitude` AS `one_city_longitude`,`c1`.`no_on_map` AS `no_on_map_one_city`, `c1`.`city_type` AS `one_city_type`,
  	`c2`.`city_name` AS `another_city_name`, `c2`.`latitude` AS `another_city_latitude`,`c2`.`longitude` AS `another_city_longitude`,`c2`.`no_on_map` AS `no_on_map_another_city`,`c2`.`city_type` AS `another_city_type`
  	FROM `route`
  	INNER JOIN `distance_route` USING(`ID_route`)
  	INNER JOIN `distance` USING (`ID_distance`)
  	INNER JOIN `city` `c1` ON `c1`.`ID_city` = `distance`.`ID_one_city`
  	INNER JOIN `city` `c2` ON `c2`.`ID_city` = `distance`.`ID_another_city`
  	WHERE (`route`.`ID_one_city` = '" . $idOneCity . "' AND   `route`.`ID_another_city` = '" . $idAnotherCity . "') OR
  	(`route`.`ID_one_city` = '" . $idAnotherCity . "' AND  `route`.`ID_another_city` = '" . $idOneCity . "')";
  	$distancesArray = $objDB->select($stmt);
  	if(!empty($distancesArray)) return self::sortRouteArray($distancesArray, $idOneCity, $idAnotherCity);
  	else return false;
  }
  
  /**
   * Функция сортирует отрезки маршрута для правильного отображения на карте
   *
   * @param array $distanceArray - массив отрезков маршрута
   * @param int $idOneCity - точка отправления/назначения
   * @param int $idAnotherCity - точка назначения/отправления
   */
  public static function sortRouteArray($distancesArray, $idOneCity, $idAnotherCity) {
  	Service_Debug::setDebug();
  	$distancesResultArray = array();
  	
  	$currentPoint = $idOneCity;
  	$i = 1;
  	$distancesInRoute = count($distancesArray);
  	while(!empty($distancesArray)) {
  		foreach($distancesArray as $key => $distanceArray) {
  			if($distanceArray['ID_one_city'] == $currentPoint) {
  				$distancesResultArray[] =  array('city_name' => $distanceArray['one_city_name'], 'ID_city' => $distanceArray['ID_one_city'], 'city_type' => $distanceArray['one_city_type'], 'distance_to_next' => $distanceArray['distance'], 'road_type' => $distanceArray['road_type'],'latitude' => $distanceArray['one_city_latitude'], 'longitude' => $distanceArray['one_city_longitude'], 'no_on_map' =>  $distanceArray['no_on_map_one_city'] == 1 ? 1 : 0 );
  				$currentPoint = $distanceArray['ID_another_city'];
  				if($i != $distancesInRoute) unset($distancesArray[$key]);
  				else {
  					$distancesResultArray[] = array('city_name' => $distanceArray['another_city_name'], 'ID_city' => $distanceArray['ID_another_city'], 'city_type' => $distanceArray['another_city_type'], 'distance_to_next' => 0, 'road_type' => 0, 'latitude' => $distanceArray['another_city_latitude'], 'longitude' => $distanceArray['another_city_longitude'],'no_on_map' =>  $distanceArray['no_on_map_another_city'] == 1 ? 1 : 0 );
  					unset($distancesArray[$key]);
  				}
  				break;
  			}
  			elseif($distanceArray['ID_another_city'] == $currentPoint) {
  				$distancesResultArray[] = array('city_name' => $distanceArray['another_city_name'],  'ID_city' => $distanceArray['ID_another_city'], 'city_type' => $distanceArray['another_city_type'], 'distance_to_next' => $distanceArray['distance'], 'road_type' => $distanceArray['road_type'], 'latitude' => $distanceArray['another_city_latitude'], 'longitude' => $distanceArray['another_city_longitude'],'no_on_map' =>  $distanceArray['no_on_map_another_city'] == 1 ? 1 : 0 );
  				$currentPoint = $distanceArray['ID_one_city'];
  				if($i != $distancesInRoute) unset($distancesArray[$key]);
  				else {
  					$distancesResultArray[] =  array('city_name' => $distanceArray['one_city_name'], 'ID_city' => $distanceArray['ID_one_city'], 'city_type' => $distanceArray['one_city_type'],  'distance_to_next' => 0, 'road_type' => 0,'latitude' => $distanceArray['one_city_latitude'], 'longitude' => $distanceArray['one_city_longitude'], 'no_on_map' =>  $distanceArray['no_on_map_one_city'] == 1 ? 1 : 0 );
  					unset($distancesArray[$key]);
  				}
  				break;
  			}
  		}
  		$i++;
  	}
  	return $distancesResultArray;
  }
	
  /**
   * Функция получает массив полпулярных маршрутов для конкретного маршрута(заданной пары городов)
   * 
   * @param array $citiesArray - массив из двух элементов - города маршрута
   */
  
  public static function getPopularRoutes($citiesArray) {
  	Service_Debug::setDebug();
  	$objDB = Service_Database::instance();

 	if($citiesArray[0]['ID_federal_district'] == $citiesArray[1]['ID_federal_district']) { // Если обе ключевые точки маршрута принадлежат одному ФО
 		$stmt = "SELECT `city`.`city_name`,`city`.`city_url`,`city`.`city_type`,`city`.`importance_level`
		 		FROM `city`
		 		WHERE `city`.`ID_city` !='" . $citiesArray[0]['ID_city']  . "' AND `city`.`ID_city` !='" . $citiesArray[1]['ID_city'] . "' AND `city`.`ID_federal_district`='" . $citiesArray[0]['ID_federal_district'] . "' AND `city`.`importance_level` >='" . City::$minImortanceToView . "'
		 		ORDER BY `city`.`city_type` DESC, `city`.`city_url`  LIMIT 28";
 	}
 	else {	// Делаем UINION, чтобы было обязательно по 7 городов каждого ФО
 		$stmt = "(SELECT `city`.`city_name`,`city`.`city_url`,`city`.`city_type`,`city`.`importance_level`
 				FROM `city`
 				WHERE `city`.`ID_city` !='" . $citiesArray[0]['ID_city']  . "' AND `city`.`ID_city` !='" . $citiesArray[1]['ID_city'] . "' AND `city`.`ID_federal_district`='" . $citiesArray[0]['ID_federal_district'] . "' AND `city`.`importance_level` >='" . City::$minImortanceToView . "'
 				ORDER BY `city`.`city_type` DESC, `city`.`city_url`  LIMIT 14)
 				UNION
 				(SELECT `city`.`city_name`,`city`.`city_url`,`city`.`city_type`,`city`.`importance_level`
 				FROM `city`
 				WHERE `city`.`ID_city` !='" . $citiesArray[0]['ID_city']  . "' AND `city`.`ID_city` !='" . $citiesArray[1]['ID_city'] . "' AND `city`.`ID_federal_district`='" . $citiesArray[1]['ID_federal_district'] . "' AND `city`.`importance_level` >='" . City::$minImortanceToView . "'
 				ORDER BY `city`.`city_type` DESC, `city`.`city_url`  LIMIT 14)";
 	}
  	
  	return $objDB->select($stmt);
  }
  
  /**
   * Функция получает url-маршрута для двух заданных городов
   */
  public static function getRouteUrl($oneCityName, $anotherCityName) {
  	Service_Debug::setDebug();
  	$objDB = Service_Database::instance();
  	$stmt = "SELECT `city`.`city_name`,`city`.`city_url`,`city`.`city_type`
  	FROM `city`
  	WHERE `city`.`city_name` ='" . $oneCityName  . "' OR `city`.`city_name` ='" . $anotherCityName . "' ORDER BY `city`.`city_type` DESC, `city`.`city_url` ";
  
  	$result = $objDB->select($stmt);
  	if(empty($result[0]) || empty($result[1])) return false;
  
  	$urlString = '<a href="' . W_ABSPATH . 'routes/' . $result[0]['city_url'] . '-' . $result[1]['city_url'] . '">' . $oneCityName . ' - ' . $anotherCityName . '</a>';
  	return $urlString;
  }
  
  /**
   * Функция получает составной маршрут для маршрутов, которых нет в БД. Дл поиска используются 30 крупнейших городов
   * 
   * @param int $idOneCity - id города отправления
   * @param int $idAnotherCity - id города назначения
   * @return array - массив двух массивов: 0 элемент - первая часть пути, 1 - вторая часть
   */
  public static function getCompositeRoute($idOneCity, $idAnotherCity) {
  	Service_Debug::setDebug();
   	$objDB = Service_Database::instance();
   	$stmt = "SELECT IF(`route`.`ID_one_city`='" . $idAnotherCity . "',`route`.`ID_another_city`,`route`.`ID_one_city`) ID_city,(`temp`.`distance` + `route`.`distance`) full_distance
			FROM `route`
			INNER JOIN (
					SELECT IF(`route`.`ID_one_city`='" . $idOneCity . "',`route`.`ID_another_city`,`route`.`ID_one_city`) `middle_ID`, `route`.`distance`, `city`.`city_type`
		   			FROM `route`
		   			INNER JOIN `city` ON IF(`route`.`ID_one_city`='" . $idOneCity . "',`route`.`ID_another_city`=`city`.`ID_city`,`route`.`ID_one_city`=`city`.`ID_city`)
		   			WHERE `route`.`ID_one_city`='" . $idOneCity . "' OR `route`.`ID_another_city`='" . $idOneCity . "' ORDER BY `city`.`city_type` DESC, `city`.`population` DESC LIMIT 130
					)temp
			ON `temp`.`middle_ID`= `route`.`ID_one_city` OR `temp`.`middle_ID`= `route`.`ID_another_city`
			
			WHERE `route`.`ID_one_city`='" . $idAnotherCity . "' OR `route`.`ID_another_city`='" . $idAnotherCity . "'
			ORDER BY full_distance LIMIT 1";
   	$result = $objDB->selectOne($stmt);
   	
   	if(!$result) return false;
   	
   	$firstRoutePart = self::getRouteArrayByIds($idOneCity, $result['ID_city']);
   	$secondRoutePart = self::getRouteArrayByIds($result['ID_city'], $idAnotherCity);
   	unset($firstRoutePart[count($firstRoutePart) - 1]);
   	
   	return array_merge($firstRoutePart, $secondRoutePart);
   }
   
}
?>